package com.microbroadcast.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.microbroadcast.resource.Area;
import com.microbroadcast.resource.Comment;
import com.microbroadcast.resource.Location;
import com.microbroadcast.resource.Message;
import com.microbroadcast.resource.UserBasic;

public class MessagePersistence {

	public static List<Message> getMessageList(long areaID, int num) {
		ArrayList<Message> result = new ArrayList<Message>();
		try {
			Connection c = DBHelper.getConnection();
			String sql = "select * from message where AreaID = ? order by MessageID desc limit ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setLong(1, areaID);
			ps.setInt(2, num);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				long messageID = rs.getLong("MessageID");
				long userID = rs.getLong("UserID");
				UserBasic user = UserBasicPersistence.getUserBasic(userID);
				Date createAt = rs.getDate("CreateAt");
				int commentsCount = rs.getInt("CommentsCount");
				String text = rs.getString("Text");
				long locationID = rs.getLong("LocationID");
				Location location = LocationPersistence.getLocation(locationID);
				Area area = AreaPersistence.getArea(areaID);
				List<Comment> comments = CommentPersistence
						.getCommentsByMessageID(messageID);
				Message m = new Message(messageID, text, createAt, location,
						area, user, commentsCount, comments);
				result.add(m);
			}
			rs.close();
			ps.close();
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return result;
		// TODO Auto-generated method stub
	}

	public static List<Message> getMessageListBefore(long areaID, int num,
			long maxID) {
		ArrayList<Message> result = new ArrayList<Message>();
		try {
			Connection c = DBHelper.getConnection();
			String sql = "select * from message where AreaID = ? and MessageID < ? order by MessageID desc limit ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setLong(1, areaID);
			ps.setLong(2, maxID);
			ps.setInt(3, num);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				long messageID = rs.getLong("MessageID");
				long userID = rs.getLong("UserID");
				UserBasic user = UserBasicPersistence.getUserBasic(userID);
				Date createAt = rs.getDate("CreateAt");
				int commentsCount = rs.getInt("CommentsCount");
				String text = rs.getString("Text");
				long locationID = rs.getLong("LocationID");
				Location location = LocationPersistence.getLocation(locationID);
				Area area = AreaPersistence.getArea(areaID);
				List<Comment> comments = CommentPersistence
						.getCommentsByMessageID(messageID);
				Message m = new Message(messageID, text, createAt, location,
						area, user, commentsCount, comments);
				result.add(m);
			}
			rs.close();
			ps.close();
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return result;
		// TODO Auto-generated method stub

	}

	public static List<Message> getMessageListAfter(long areaID, int num,
			long sinceID) {
		ArrayList<Message> result = new ArrayList<Message>();
		try {
			Connection c = DBHelper.getConnection();
			String sql = "select * from message where AreaID = ? and MessageID > ? order by MessageID desc limit ?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setLong(1, areaID);
			ps.setLong(2, sinceID);
			ps.setInt(3, num);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				long messageID = rs.getLong("MessageID");
				long userID = rs.getLong("UserID");
				UserBasic user = UserBasicPersistence.getUserBasic(userID);
				Date createAt = rs.getDate("CreateAt");
				int commentsCount = rs.getInt("CommentsCount");
				String text = rs.getString("Text");
				long locationID = rs.getLong("LocationID");
				Location location = LocationPersistence.getLocation(locationID);
				Area area = AreaPersistence.getArea(areaID);
				List<Comment> comments = CommentPersistence
						.getCommentsByMessageID(messageID);
				Message m = new Message(messageID, text, createAt, location,
						area, user, commentsCount, comments);
				result.add(m);
			}
			rs.close();
			ps.close();
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return result;
		// TODO Auto-generated method stub

	}
}